﻿namespace Hidistro.SqlDal.Store
{
    using Hidistro.Core;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;

    public class HotkeywordDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public void AddHotkeywords(int categoryId, string Keywords)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Hotkeywords_Log");
            this.database.AddInParameter(storedProcCommand, "Keywords", DbType.String, Keywords);
            this.database.AddInParameter(storedProcCommand, "CategoryId", DbType.Int32, categoryId);
            this.database.AddInParameter(storedProcCommand, "SearchTime", DbType.DateTime, DateTime.Now);
            this.database.ExecuteNonQuery(storedProcCommand);
        }

        public void DeleteHotKeywords(int hId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(" Delete FROM Hishop_Hotkeywords Where Hid =@Hid");
            this.database.AddInParameter(sqlStringCommand, "Hid", DbType.Int32, hId);
            this.database.ExecuteNonQuery(sqlStringCommand);
        }

        public DataSet GetAllHotKeywords()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT CategoryId, Name AS CategoryName, RewriteName FROM Hishop_Categories WHERE Depth = 1 ORDER BY DisplaySequence; SELECT * FROM Hishop_Hotkeywords ORDER BY Frequency DESC");
            DataSet set = this.database.ExecuteDataSet(sqlStringCommand);
            set.Relations.Add("relation", set.Tables[0].Columns["CategoryId"], set.Tables[1].Columns["CategoryId"], false);
            return set;
        }

        public string GetHotkeyword(int id)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT Keywords FROM Hishop_Hotkeywords WHERE Hid=@Hid");
            this.database.AddInParameter(sqlStringCommand, "Hid", DbType.Int32, id);
            return this.database.ExecuteScalar(sqlStringCommand).ToString();
        }

        public DataTable GetHotKeywords()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT h.*,t.Name AS CategoryName,t.RewriteName,(select RewriteName from Hishop_Categories where CategoryId=t.ParentCategoryId) as ParentRewriteName FROM Hishop_Hotkeywords h left join Hishop_Categories t on t.CategoryId=h.CategoryId ORDER BY Frequency DESC");
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetHotKeywords(int categoryId, int hotKeywordsNum)
        {
            string query = string.Format("SELECT TOP {0} t1.*,t2.RewriteName,(select RewriteName from Hishop_Categories where CategoryId=t2.ParentCategoryId) as ParentRewriteName FROM Hishop_Hotkeywords t1 left join Hishop_Categories t2 on t2.CategoryId=t1.CategoryId", hotKeywordsNum);
            if (categoryId != 0)
            {
                query = query + string.Format(" WHERE t1.CategoryId = {0}", categoryId);
            }
            query = query + " ORDER BY Frequency DESC";
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public void SwapHotWordsSequence(int hid, int replaceHid, int displaySequence, int replaceDisplaySequence)
        {
            DataHelper.SwapSequence("Hishop_Hotkeywords", "Hid", "Frequency", hid, replaceHid, displaySequence, replaceDisplaySequence);
        }

        public void UpdateHotWords(int hid, int categoryId, string hotKeyWords)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("Update Hishop_Hotkeywords Set CategoryId = @CategoryId, Keywords =@Keywords Where Hid =@Hid");
            this.database.AddInParameter(sqlStringCommand, "Hid", DbType.Int32, hid);
            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, categoryId);
            this.database.AddInParameter(sqlStringCommand, "Keywords", DbType.String, hotKeyWords);
            this.database.ExecuteNonQuery(sqlStringCommand);
        }
    }
}

